package cn.com.lms.business.learning.persistence.repository;

import cn.com.lms.business.learning.persistence.entity.FbLearningStageDetail;
import com.bnzj.core.persistence.repository.BaseRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

@Repository
public interface LearningStageDetailRepository extends BaseRepository<FbLearningStageDetail, Long> {


    @Query(value = "select a.id,a.stage_id as stageId,a.plan_id planId,a.limit_num as limitNum,a.adopt_score as adoptScore,(select plan_name from bnz_learning_plan lp where lp.id=a.plan_id) planName,a.obj_id as objId,a.study_type as studyType," +
            "(case study_type when 0 then (SELECT sdy.`name` FROM bnz_course_info ci,bnz_sys_dictionary sdy  WHERE ci.course_type=sdy.id and  ci.id = obj_id) else null end) courseType,a.courseTime,a.study_time as studyTime,a.finish as finishNum,a.unfinish as unFinish" +
            ",a.objName as objName,a.objTitle,(select IFNULL(sum( ep.paper_score),0)  from bnz_exam_paper_extra ep where ep.paper_id=a.obj_id)paperScore from(SELECT ld.*,(CASE WHEN ld.study_type = 0 THEN" +
            " (SELECT title FROM bnz_course_info ci WHERE ci.id = ld.obj_id ) WHEN ld.study_type = 1 THEN" +
            " (SELECT qi.questionnaire_name FROM bnz_questionnaire_info qi WHERE qi.id = ld.obj_id ) WHEN ld.study_type = 2 THEN" +
            " (SELECT ep.paper_name FROM bnz_exam_paper ep  WHERE ep.id = ld.obj_id ) ELSE NULL END ) objName," +
            "(CASE WHEN ld.study_type = 0 THEN( SELECT title FROM bnz_course_info ci WHERE ci.id = ld.obj_id ) ELSE obj_name END ) objTitle," +
            " (CASE\tWHEN ld.study_type = 0 THEN( SELECT course_time FROM bnz_course_info ci WHERE ci.id = ld.obj_id )   ELSE 0\tEND ) courseTime," +
            " ( SELECT count( ls.id ) FROM bnz_learning_student ls WHERE ld.id = ls.stage_detail_id ) AS unfinish," +
            " ( SELECT count( ls.id ) FROM bnz_learning_student ls WHERE ld.id = ls.stage_detail_id AND ls.finish_state = '1' ) AS finish " +
            "FROM   bnz_learning_stage_detail ld WHERE ld.stage_id = ?1) a", nativeQuery = true)
    List<Map<String, Object>> queryListByStageId(Long id);


    @Query(value = "SELECT ld.id ,ld.stage_id as stageId,ld.obj_id objId,ld.study_type studyType,ld.plan_id planId,\n" +
            "(select plan_name from bnz_learning_plan WHERE id=ld.plan_id) planName,( SELECT sdy.`name` FROM   bnz_sys_dictionary sdy WHERE ci.course_type = sdy.id  ) courseType,\n" +
            "(CASE WHEN ld.study_type = 0 THEN\n" +
            "(SELECT ci.course_time FROM bnz_course_info ci WHERE ci.id = ld.obj_id ) WHEN ld.study_type = 2 THEN ld.study_time ELSE NULL END) studyTime,\n" +
            " lst.learning_hours learningHours,IFNULL(lst.finish_state,0) finishState,\n" +
            "ci.picture_url pictureUrl,ci.live_start_time liveStartTime,ci.live_real_start_time liveRealStartTime,ci.live_status liveStatus," +
            "(CASE WHEN ld.study_type = 0 THEN( SELECT title FROM bnz_course_info ci WHERE ci.id = ld.obj_id )  ELSE ld.obj_name END) objName," +
            "(CASE WHEN ld.study_type = 0 THEN\n" +
            "( SELECT DATE_FORMAT(ci.create_time,'%Y-%m-%d') FROM bnz_course_info ci WHERE ci.id = ld.obj_id ) \n" +
            "WHEN ld.study_type = 1 THEN\n" +
            "( SELECT DATE_FORMAT(qi.create_time,'%Y-%m-%d') FROM bnz_questionnaire_info qi WHERE qi.id = ld.obj_id ) \n" +
            "WHEN ld.study_type = 2 THEN\n" +
            "( SELECT DATE_FORMAT(ep.create_time,'%Y-%m-%d') FROM bnz_exam_paper ep WHERE ep.id = ld.obj_id ) ELSE NULL \n" +
            "END ) createTime,\n" +
            "( SELECT count( ls.id ) FROM bnz_learning_student ls WHERE ld.id = ls.stage_detail_id ) AS unfinish,\n" +
            "( SELECT count( ls.id ) FROM bnz_learning_student ls WHERE ld.id = ls.stage_detail_id AND ls.finish_state = '1' ) AS finish \n" +
            "FROM bnz_learning_stage_detail ld left join bnz_course_info ci on ld.obj_id=ci.id" +
            " left join bnz_learning_student lst on lst.stage_detail_id=ld.id  and lst.user_id=?2 WHERE ld.stage_id = ?1", nativeQuery = true)
    List<Map<String, Object>> queryStageDetail(Long id, Long userId);


    @Query(value = " SELECT (CASE WHEN ld.study_type = 0 THEN( SELECT title FROM bnz_course_info ci WHERE ci.id = ld.obj_id ) ELSE obj_name END ) objTitle," +
            "( CASE WHEN ld.study_type = 0 THEN ( SELECT ci.course_time FROM bnz_course_info ci WHERE ci.id = ld.obj_id ) ELSE ls.finish_state END ) finishState," +
            "learning_hours as studyTime,ld.study_type studyType from bnz_learning_student ls " +
            " left join bnz_learning_stage_detail ld on ld.id = ls.stage_detail_id where ls.user_id = ?1 and ld.stage_id = ?2", nativeQuery = true)
    List<Map<String, Object>> queryListByStudentId(Long userId, Long stageId);


    /**
     * 查询员工学习计划阶段详情
     *
     * @param planId
     * @param userId
     * @return
     */
    @Query(value = "select (case lsd.study_type when 0 then  (select title from bnz_course_info ci where ci.id=lsd.obj_id)else lsd.obj_name end) objName,\n" +
            "(case lsd.study_type when 0 then  (select ci.course_type from bnz_course_info ci where ci.id=lsd.obj_id) else null end) courseType,\n" +
            "(case lsd.study_type when 0 then (select ci.course_time from bnz_course_info ci where ci.id=lsd.obj_id)else 0 end) courseTime\n" +
            ",lsd.study_type as objType,0 examScore,ls.finish_state as finishSituation,ls.learning_hours learningHours from bnz_learning_student ls \n" +
            "left join bnz_learning_stage_detail lsd on ls.stage_detail_id=lsd.id\twhere lsd.stage_id=?1 and user_id=?2 GROUP BY lsd.id", nativeQuery = true)
    List<Map<String, Object>> queryUserLearningStageView(Long stageId, Long userId);



    /**
     * 用户通过测试 更新完成状态为1
     *
     * @param stageDetailId
     * @param employeeId
     * @return
     */
    @Transactional
    @Modifying
    @Query(value = "update bnz_learning_student set finish_state =1,last_study_time=now() WHERE stage_detail_id=?1 and user_id=?2", nativeQuery = true)
    int updateUserFinishState(Long stageDetailId, Long employeeId);

    /**
     * 更新时间
     *
     * @param stageDetailId
     * @param employeeId
     * @return
     */
    @Transactional
    @Modifying
    @Query(value = "update bnz_learning_student set last_study_time=now()  WHERE stage_detail_id=?1 and user_id=?2", nativeQuery = true)
    int updateUserLastStudyTime(Long stageDetailId, Long employeeId);

    @Query(value = "select sd.id,sd.plan_id as planId,sd.stage_id as stageId,sd.obj_id as objId,sd.limit_num as limitNum, " +
            " sd.adopt_score as adoptScore,sd.study_time as paperTime, lp.finish_time as finishTime,lp.cover_url as coverUrl,lp.score as credit, lp.plan_name as planName,ls.stage_name as stageName, " +
            " ep.paper_name as paperName,pe.subject_count as subjectCount,pe.paper_score as paperScore " +
            " from bnz_learning_stage_detail sd LEFT JOIN bnz_learning_plan lp on sd.plan_id=lp.id " +
            " LEFT JOIN bnz_learning_stage ls on ls.id=sd.stage_id LEFT JOIN bnz_exam_paper ep on sd.obj_id=ep.id " +
            " LEFT JOIN bnz_exam_paper_extra pe on sd.obj_id=pe.paper_id  where sd.study_type=2 and sd.id=?1", nativeQuery = true)
    Map<String, Object> getTestLearningStageDetail(Long stageDetailId);


    /**
     * 删除阶段下所有的详情数据
     *
     * @param stageId
     * @return
     */
    @Modifying
    @Query(value = "delete from  bnz_learning_stage_detail where plan_id=?1", nativeQuery = true)
    int deleteByPlanId(Long stageId);
}
